package com.htdata.iiot;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;

import com.htdata.iiot.api.pojo.Device;
import com.htdata.iiot.api.pojo.DeviceDAO;
import com.htdata.iiot.api.pojo.Metric;


public class Main {
	private static Map<String, Integer> deviceMap = new HashMap<>();
	public static void main(String[] args) throws FileNotFoundException, IOException, SQLException {
		//1.获取设备信息
		List<List<String>> allList = new ArrayList<>();
		String [] files = {
				"C:\\Users\\SunMy\\Desktop\\海顿设备台账（已统计）\\设备台帐-电气设备-设备.xls",
				"C:\\Users\\SunMy\\Desktop\\海顿设备台账（已统计）\\设备台帐-动设备-设备.xls",
				"C:\\Users\\SunMy\\Desktop\\海顿设备台账（已统计）\\设备台帐-静设备-设备.xls",
				"C:\\Users\\SunMy\\Desktop\\海顿设备台账（已统计）\\设备台帐-仪表部分设备-设备.xls"
				
		};
		for(String str :files){
			List<List<String>> excelAsFile = getExcelAsFile(str,1);
			allList.addAll(excelAsFile);
		}
		
		//2.转换成对象
		List<Device> ds = convDevice(allList);
		//创建设备
		for(Device dev:ds){
			DeviceDAO.createDevice(dev);
			deviceMap.put(dev.getSerNum(), dev.getId());
		}
		System.out.println("设备创建完成，创建设备："+deviceMap.size()+" 个");
		//3.读取测点数据
		
		String [] metricFiles = {
				"C:\\Users\\SunMy\\Desktop\\海顿设备台账（已统计）\\设备台帐-电气设备-测点.xls",
				"C:\\Users\\SunMy\\Desktop\\海顿设备台账（已统计）\\设备台帐-动设备-测点.xls",
				"C:\\Users\\SunMy\\Desktop\\海顿设备台账（已统计）\\设备台帐-静设备-测点.xls",
				"C:\\Users\\SunMy\\Desktop\\海顿设备台账（已统计）\\设备台帐-仪表部分设备-测点.xls"
				
		};
		List<Metric> list = new ArrayList<>();
		for(String str :metricFiles){
			List<List<String>> excelAsFile = getExcelAsFile(str,0);
			//转换
			List<Metric> convMetric = convMetric(excelAsFile);
			list.addAll(convMetric);
		}
		//测点入库
		for(Metric metric:list){
			DeviceDAO.createComponent(metric);
		}
		
		System.out.println("测点创建完成，创建测点："+list.size()+" 个");
	}
	
	private static List<Device> convDevice(List<List<String>> allList) {
		List<Device> ds =  new ArrayList<>();
		for(List<String> ls :allList){
			Device dev = new Device();
			dev.setName(ls.get(0));
			dev.setSerNum(ls.get(1));
			dev.setLocation(ls.get(2));
			dev.setProv(ls.get(3));
			dev.setCity(ls.get(4));
			dev.setCounty(ls.get(5));
			dev.setBrand(ls.get(6));
			dev.setModel(ls.get(7));
			ds.add(dev);
		}
		return ds;
	}
	private static List<Metric> convMetric(List<List<String>> excelAsFile) {
		List<Metric> ds =  new ArrayList<>();
		List<String> header = excelAsFile.get(0);
		
		for(int i=1;i<excelAsFile.size();i++){
			List<String> ls = excelAsFile.get(i);
			
			String serNum = ls.get(0);
			String DeviceName  = ls.get(1);
			
			for(int j=2;j<ls.size();j++){
				String values = ls.get(j);
				if(values!=null&&!values.trim().isEmpty()){
					
					String componentName = header.get(j);
					Metric metric = new Metric();
					metric.setComponentName(componentName);
					metric.setDeviceName(DeviceName);
					metric.setDid(deviceMap.get(serNum));
					String metricName = null;
					String unit = null;
					switch (componentName) {
					case "操作温度°C":
						metricName = "Operating_temperature";
						unit="°C";
						break;
					case "操作压力Mpa":
						metricName = "Operating_pressure";
						unit="Mpa";
						break;
					case "壳程操作压力":
						metricName = "pressure1";
						unit="Mpa";
						break;
					case "管程操作压力":
						metricName = "pressure2";
						unit="Mpa";
						break;
					case "B段操作压力":
						metricName = "pressure3";
						unit="Mpa";
						break;
					case "A侧入口操作压力":
						metricName = "pressure4";
						unit="Mpa";
						break;
					case "A侧出口操作压力":
						metricName = "pressure5";
						unit="Mpa";
						break;
					case "A段操作压力":
						metricName = "pressure6";
						unit="Mpa";
						break;
					case "入口操作温度":
						metricName = "temperature7";
						unit="°C";
						break;
					case "出口操作温度":
						metricName = "temperature8";
						unit="°C";
						break;
					case "入口操作压力":
						metricName = "pressure9";
						unit="Mpa";
						break;
					case "出口操作压力":
						metricName = "pressure10";
						unit="Mpa";
						break;
						
					}
					metric.setName(metricName);
					metric.setSerialNumber("");
					metric.setTid(4557);
					metric.setUnit(unit);
					
					String[] split = values.split("~");
					if(split.length==1){
						split = values.split("～");
					}
					double[] db = new double[2];
					try {
						if(split.length>0){
							db[0] = Double.parseDouble(split[0].trim());
						}
						if(split.length>1){
							db[1] = Double.parseDouble(split[1].trim());
						}
					} catch (NumberFormatException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					
					if(db.length>1&&db[0]>db[1]){
						double d1 = db[0];
						db[0] = db[1];
						db[1] = d1;
					}
					if(db.length>0){
						metric.setMin(db[0]);
					}
					if(db.length>1){
						metric.setMax(db[1]);
					}
					ds.add(metric);
				}
				
			}
			
			
			
		}
		return ds;
	}

	/**
	 * 读取excel
	 * @param file
	 * @param begin
	 * @return
	 * @throws FileNotFoundException
	 * @throws IOException
	 */
	 public static  List<List<String>> getExcelAsFile(String file,int begin) throws FileNotFoundException, IOException{    
	       
		 List<List<String>> lists= new ArrayList<List<String>>();
		 
		 //1.得到Excel常用对象    
//	      POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("d:/FTP/test.xls"));    
	        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));    
	        //2.得到Excel工作簿对象    
	        HSSFWorkbook wb = new HSSFWorkbook(fs);    
	        //3.得到Excel工作表对象    
	        HSSFSheet sheet = wb.getSheetAt(0);    
	        //总行数    
	        int trLength = sheet.getLastRowNum();    
	        //4.得到Excel工作表的行    
	        HSSFRow row = sheet.getRow(0);    
	        //总列数    
	        int tdLength = row.getLastCellNum();    
	        //6.得到单元格样式    
	        for(int i=begin;i<trLength;i++){    
	            //得到Excel工作表的行    
	            HSSFRow row1 = sheet.getRow(i);   
	            List<String> l = new ArrayList<>();
	            for(int j=0;j<tdLength;j++){    
	                    
	            //得到Excel工作表指定行的单元格    
	            HSSFCell cell1 = row1.getCell(j);  
	               
	            /**  
	             * 为了处理：Excel异常Cannot get a text value from a numeric cell  
	             * 将所有列中的内容都设置成String类型格式  
	             */    
	            if(cell1!=null){    
	                  cell1.setCellType(Cell.CELL_TYPE_STRING);    
	                  //获得每一列中的值    
	                  l.add(cell1.getStringCellValue());    
	             }    
	                
	            }
	            lists.add(l);
	        }  
	        return lists;
	    }    


}